﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace NeuroForecast
{
    public class MSSQLTools
    {
        private SqlConnection _connection;
        public ConnectionState ConnectionState { set; get; }

        public Boolean Connect()
        {
            try
            {
                _connection = new SqlConnection("Data Source=(local);Initial Catalog=NeuroForecastDB;Trusted_Connection=True;");
                _connection.Open();
                ConnectionState = _connection.State;
                return true;
            }
            catch (Exception)
            {
                ConnectionState = _connection.State;
                return false;
            }
        }
        public void Disconnect()
        {
            _connection.Close();
        }

        public DataTable ExecuteQuery(string query)
        {
            SqlCommand f = new SqlCommand(query, _connection);
            SqlDataReader fbr = f.ExecuteReader();
            int fieldCount = fbr.FieldCount;
            int i;
            object[] o = new object[fieldCount];
            DataTable dt = new DataTable();
            for (i = 0; i < fieldCount; i++) dt.Columns.Add();
            while (fbr.Read())
            {
                for (i = 0; i < fieldCount; i++)
                    o[i] = fbr.GetValue(i);
                dt.Rows.Add(o);
            }
            fbr.Close();
            return dt;
        }

        public DataTable ExecuteStoredProcedure(String procName, String[] paramNames ,object[] parameters)
        {
            SqlCommand f = new SqlCommand(procName, _connection);
            f.CommandType = CommandType.StoredProcedure;
            for (int j = 0; j< parameters.Length; j++) f.Parameters.Add(new SqlParameter(paramNames[j], parameters[j]));
            SqlDataReader fbr = f.ExecuteReader();
            int fieldCount = fbr.FieldCount;
            int i;
            object[] o = new object[fieldCount];
            DataTable dt = new DataTable();
            for (i = 0; i < fieldCount; i++) dt.Columns.Add();
            while (fbr.Read())
            {
                for (i = 0; i < fieldCount; i++)
                    o[i] = fbr.GetValue(i);
                dt.Rows.Add(o);
            }
            fbr.Close();
            return dt;
        }

        //public void ExecuteQuery(string query)
        //{
        //    (new SqlCommand(query, _connection)).ExecuteNonQuery();
        //}
    }
}
